package cm.epet.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import cm.epet.ConnectionHelper;
import cm.epet.dao.Interface.IPetOwnerDAO;
import cm.epet.model.Pet;
import cm.epet.model.PetOwner;

public class PetOwnerDAO implements IPetOwnerDAO{
	java.sql.Date currentDate = new java.sql.Date(System.currentTimeMillis());
	public double buy(Pet pet,PetOwner po) throws SQLException {
		Connection con = null;
		// TODO Auto-generated method stub
		try {
			con = ConnectionHelper.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement stmt = con.createStatement();
		ResultSet rs = null;
		double balance=0;
		double result = 0;
		rs = stmt.executeQuery("select name from pet where name='"+pet.getName()+"' and owner_name!='"+po.getName()+"' ");
		if(rs.next()) {
		rs = stmt.executeQuery("select money from petowner where name='"+po.getName()+"'");
		rs.next();
		if(pet.getPrice()>rs.getDouble(1)) {
			result = 1;
		}
		else {
			balance = rs.getDouble(1)-pet.getPrice();
		    stmt.executeUpdate(" update petowner set money='"+balance+"' where name='"+po.getName()+"'");
			stmt.executeUpdate("update pet set owner_name='"+po.getName()+"' where name='"+pet.getName()+"'");
			result=balance;
			AccountDAO.createAccount(1, pet.getName(), pet.getOwner_name(), po.getName(), pet.getPrice(),currentDate );
		}
		}
		else {
			result = 2;
		}
		rs.close();
		return result;
	}
	public double sell(Pet pet,PetOwner po,String petstorename) throws SQLException {
		Connection con = null;
		// TODO Auto-generated method stub
		try {
			con = ConnectionHelper.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement stmt = con.createStatement();
		ResultSet rs = null;
		int flag = 0;
		double balance=0;
		double result=0;
		rs = stmt.executeQuery("select name from pet where name='"+pet.getName()+"' and owner_name='"+po.getName()+"' ");
		if(rs.next()) {
			rs = stmt.executeQuery("select id,name from petstore");
		    while(rs.next())
		    	System.out.println(rs.getInt(1)+"."+rs.getString(2));
		    while(flag==0) {
		    rs = stmt.executeQuery("select name from petstore where name='"+petstorename+"'");
		    if(rs.next()) {
		    	rs = stmt.executeQuery("select money from petowner where name='"+po.getName()+"'");
				rs.next();
				balance = rs.getDouble(1)+pet.getPrice();
		    	stmt.executeUpdate(" update petowner set money='"+balance+"' where name='"+po.getName()+"'");
				stmt.executeUpdate("update pet set owner_name='"+petstorename+"' where name='"+pet.getName()+"'");
				result=balance;
				AccountDAO.createAccount(0, pet.getName(), pet.getOwner_name(), petstorename, pet.getPrice(),currentDate );
				flag=1;
		    }
		    else {
		    	result=1;
			}
		    }
		}
		else {
			result=2;

		}
		rs.close();
		return result;
	}
	
	
	public PetOwner login(String name,String password) throws SQLException {
		Connection con = null;
		// TODO Auto-generated method stub
		try {
			con = ConnectionHelper.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement stmt = con.createStatement();
		ResultSet rs = null;
		PetOwner po = null;
		stmt.execute("select * from petowner where name='"+name+"' and password='"+password+"'");
		rs = stmt.getResultSet();
		if(rs.next()) {
				po = new PetOwner(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDouble(4));
			}
		rs.close();
		return po;
	}
	@Override
	public ArrayList<String> displayMyPet(PetOwner po) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		// TODO Auto-generated method stub
		try {
			con = ConnectionHelper.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement stmt = con.createStatement();
		ResultSet rs = null;
		ArrayList<String> info = new ArrayList<String>();
		//int i=1;
		rs = stmt.executeQuery("select id,name from pet where owner_name='"+po.getName()+"'");
		if(rs.next()) {
			rs.beforeFirst();
			while(rs.next()) {
				info.add(rs.getString(1));
				info.add(rs.getString(2));
		}
		}
		else {
			info=null;
		}
		rs.close();
		return info;
	}
	@Override
	public ArrayList<String> displayAvailable(PetOwner po) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		// TODO Auto-generated method stub
		try {
			con = ConnectionHelper.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement stmt = con.createStatement();
		ResultSet rs = null;
		ArrayList<String> info = new ArrayList<String>();
		//int i=1;
		rs = stmt.executeQuery("select id,name,owner_name from pet where owner_name=any(select name from petstore)");
		if(rs.next()) {
			rs.beforeFirst();
			while(rs.next()) {
				info.add(rs.getString(1));
				info.add(rs.getString(2));
				info.add(rs.getString(3));
				}
			}
		else {
			info=null;
		}
		rs.close();
		return info;
	}
	@Override
	public ArrayList<String> getAllOwner() throws SQLException {
		Connection con = null;
		// TODO Auto-generated method stub
		try {
			con = ConnectionHelper.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement stmt = con.createStatement();
		ResultSet rs = null;
		ArrayList<String> info = new ArrayList<String>();
		rs = stmt.executeQuery("select * from petowner");
		while(rs.next())
		{
			info.add(rs.getString(1));
			info.add(rs.getString(2));
		}
		rs.close();
		return info;
	}
	}
